--    Program: 		CAPCMEXT_CE_STATEMENT.sql
--    Description:	Extract CM Statement Header & Line information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   

  l_processed_count_1 	number;
  l_rejected_count_1  	number;
  l_processed_count_2	number;
  l_rejected_count_2	number;
  l_err_msg       	varchar2(2000);
  
  l_utl_file_1  		utl_file.file_type;
  l_utl_file_2 		utl_file.file_type;

  l_filename_1 		varchar2(255);
  l_filename_2 		varchar2(255);
  l_pathname  		varchar2(100);

  l_ngo_sob			varchar2(20);
  l_temp			varchar2(10);

  l_bg_code			varchar2(10);

  cursor cur_1 (p_bg_code varchar2) is
    select csha.statement_header_id, 
	   csha.bank_account_id, 
	   csha.statement_number, 
	   csha.statement_date, 
	   csha.created_by, 
	   csha.creation_date, 
	   csha.last_updated_by,
	   csha.last_update_date, 
	   csha.auto_loaded_flag, 
	   csha.gl_date, 
	   csha.control_begin_balance, 
	   csha.control_total_dr, 
	   csha.control_total_cr, 
	   csha.control_end_balance, 
	   csha.control_dr_line_count, 
	   csha.control_cr_line_count, 
	   csha.currency_code, 
	   csha.last_update_login, 
	   csha.attribute_category,
	   csha.attribute1, 
	   csha.attribute2, 
	   csha.attribute3, 
	   csha.attribute4, 
	   csha.attribute5, 
	   csha.attribute6, 
	   csha.attribute7, 
	   csha.attribute8, 
	   csha.attribute9, 
	   csha.attribute10,
	   csha.attribute11, 
	   csha.attribute12, 
	   csha.attribute13, 
	   csha.attribute14, 
	   csha.attribute15, 
	   csha.statement_complete_flag, 
	   csha.org_id, 
	   csha.doc_sequence_id, 
	   csha.doc_sequence_value, 
	   csha.check_digits, 
	   csha.cashflow_balance, 
	   csha.int_calc_balance
from ce_statement_headers_all csha, hr_all_organization_units haou
where csha.org_id = haou.organization_id
and haou.name like 'OU_'||P_BG_CODE||'%'
order by csha.rowid;


cursor cur_2 (p_bg_code varchar2) is
	select csl.statement_line_id,
	   csl.statement_header_id, 
	   csl.line_number, 
	   csl.trx_date,
	   csl.trx_type,
	   csl.amount,
	   csl.status,
	   csl.created_by, 
	   csl.creation_date, 
	   csl.last_updated_by,
	   csl.last_update_date, 
	   csl.trx_code_id, 
	   csl.effective_date, 
	   csl.bank_trx_number,
	   csl.trx_text, 
	   csl.customer_text,
	   csl.invoice_text,
	   csl.bank_account_text,
	   csl.currency_code,
	   csl.exchange_rate_type,
	   csl.exchange_rate,
	   csl.exchange_rate_date,
	   csl.original_amount,
	   csl.charges_amount,
	   csl.last_update_login,
	   csl.reconcile_to_statement_flag,
	   csl.attribute_category,
	   csl.attribute1, 
	   csl.attribute2, 
	   csl.attribute3, 
	   csl.attribute4, 
	   csl.attribute5, 
	   csl.attribute6, 
	   csl.attribute7, 
	   csl.attribute8, 
	   csl.attribute9, 
	   csl.attribute10,
	   csl.attribute11, 
	   csl.attribute12, 
	   csl.attribute13, 
	   csl.attribute14, 
	   csl.attribute15, 
	   csl.ce_statement_lines
	   from ce_statement_headers_all csha, ce_statement_lines csl, hr_all_organization_units haou
where csha.org_id = haou.organization_id
and csha.statement_header_id = csl.statement_header_id
and haou.name like 'OU_'||P_BG_CODE||'%'
order by csha.rowid, csl.rowid;



begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPCM_CE_STATEMENT_HEADERS_ALL.dat';
  l_filename_2 := 'CAPCM_CE_STATEMENT_LINES.dat';
  
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w', 32767);
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w', 32767);
  
  l_processed_count_1 := 0;
  l_rejected_count_1  := 0;
  l_processed_count_2 := 0;
  l_rejected_count_2  := 0;
                                         
  l_ngo_sob := '&1';
  l_bg_code := substr(l_ngo_sob,(instr(l_ngo_sob,'C')),4);

  begin
 	select distinct substr(name,4,4) into l_temp
	 	from hr_all_organization_units
	 	where name like 'OU_'|| l_bg_code ||'%';

   exception
     when others then
       dbms_output.put_line(l_bg_code ||': BG name not found');
       raise;
   end;    
  
    for cur1 in cur_1(l_bg_code) loop   	
     
     -- output data
     utl_file.put_line( l_utl_file_1, 
	  			cur1.statement_header_id || chr(9) ||
				cur1.bank_account_id || chr(9) ||
				cur1.statement_number || chr(9) ||
	  			cur1.statement_date || chr(9) ||
	  			cur1.created_by || chr(9) ||
	  			cur1.creation_date || chr(9) ||
	  			cur1.last_updated_by || chr(9) ||
	  			cur1.last_update_date || chr(9) ||
	  			cur1.auto_loaded_flag || chr(9) ||
	  			cur1.gl_date || chr(9) ||
	  			cur1.control_begin_balance || chr(9) ||
	  			cur1.control_total_dr || chr(9) ||
	  			cur1.control_total_cr || chr(9) ||
	  			cur1.control_end_balance || chr(9) ||
	  			cur1.control_dr_line_count || chr(9) ||
	  			cur1.control_cr_line_count || chr(9) ||
	  			cur1.currency_code || chr(9) ||
	  			cur1.last_update_login || chr(9) ||
	  			cur1.attribute_category || chr(9) ||
	  			cur1.attribute1 || chr(9) ||              
	  			cur1.attribute2 || chr(9) ||          
	  			cur1.attribute3 || chr(9) ||          
	  			cur1.attribute4 || chr(9) ||                              
	  			cur1.attribute5 || chr(9) ||                  
	  	  		cur1.attribute6 || chr(9) ||                  
	  			cur1.attribute7 || chr(9) ||                  
	  			cur1.attribute8 || chr(9) ||                  
	  			cur1.attribute9 || chr(9) ||                  
	  			cur1.ATTRIBUTE10 || chr(9) ||                  
	  			cur1.ATTRIBUTE11 || chr(9) ||                 
	  			cur1.ATTRIBUTE12 || chr(9) ||                 
	  			cur1.ATTRIBUTE13 || chr(9) ||                 
	  			cur1.ATTRIBUTE14 || chr(9) ||                 
	  			cur1.ATTRIBUTE15 || chr(9) ||                 
	  			cur1.statement_complete_flag || chr(9) ||
	  			cur1.org_id || chr(9) ||
	  			cur1.doc_sequence_id || chr(9) ||
	  			cur1.doc_sequence_value || chr(9) ||
	  			cur1.check_digits|| chr(9) ||
				cur1.cashflow_balance|| chr(9) ||
	  			cur1.int_calc_balance
	    );

  	    l_processed_count_1 := l_processed_count_1 + 1;
       end loop;
     
	 for cur2 in cur_2(l_bg_code) loop
	 	  utl_file.put_line( l_utl_file_2,
		  			   cur2.statement_line_id || chr(9) ||
                                           cur2.statement_header_id || chr(9) ||
					   cur2.line_number || chr(9) ||
					   cur2.trx_date || chr(9) ||
					   cur2.trx_type|| chr(9) ||
					   cur2.amount || chr(9) ||
					   cur2.status || chr(9) ||
					   cur2.created_by || chr(9) ||
					   cur2.creation_date || chr(9) ||
					   cur2.last_updated_by || chr(9) ||
					   cur2.last_update_date || chr(9) ||
					   cur2.trx_code_id || chr(9) ||
					   cur2.effective_date || chr(9) ||
					   cur2.bank_trx_number || chr(9) ||
					   cur2.trx_text || chr(9) ||
					   cur2.customer_text || chr(9) ||
					   cur2.invoice_text || chr(9) ||
					   cur2.bank_account_text || chr(9) ||
					   cur2.currency_code || chr(9) ||
					   cur2.exchange_rate_type || chr(9) ||
					   cur2.exchange_rate || chr(9) ||
					   cur2.exchange_rate_date || chr(9) ||
					   cur2.original_amount || chr(9) ||
					   cur2.charges_amount || chr(9) ||
					   cur2.last_update_login || chr(9) ||
					   cur2.reconcile_to_statement_flag || chr(9) ||
					   cur2.attribute_category || chr(9) ||
					   cur2.attribute1 || chr(9) ||
					   cur2.attribute2 || chr(9) ||
					   cur2.attribute3 || chr(9) ||
					   cur2.attribute4 || chr(9) ||
					   cur2.attribute5 || chr(9) ||
					   cur2.attribute6 || chr(9) ||
					   cur2.attribute7 || chr(9) ||
					   cur2.attribute8 || chr(9) ||
					   cur2.attribute9 || chr(9) ||
					   cur2.attribute10 || chr(9) ||
					   cur2.attribute11 || chr(9) ||
					   cur2.attribute12 || chr(9) ||
					   cur2.attribute13 || chr(9) ||
					   cur2.attribute14 || chr(9) ||
					   cur2.attribute15 || chr(9) ||
          				   cur2.ce_statement_lines
		  ); 
		  
		  l_processed_count_2 := l_processed_count_2 + 1;
  	  end loop;
  	    
   l_err_msg := '';
   if l_processed_count_1 = 0 then   
     l_rejected_count_1 := l_rejected_count_1 + 1;
     l_err_msg := l_bg_code || ': bank statement header not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPCMEXT_CE_STATEMENT_HEADERS_ALL', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );
   
   commit;
   
   l_err_msg := '';
   if l_processed_count_2 = 0 then   
     l_rejected_count_2 := l_rejected_count_2 + 1;
     l_err_msg := l_bg_code || ': bank statement line not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPPOEXT_CE_STATEMENT_LINES', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );
   
   commit;

  utl_file.fclose(l_utl_file_1);
  utl_file.fclose(l_utl_file_2);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/

--exit
